﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;

namespace MarginTrading.Services
{
    public class CurrentAccBalanceService
    {

        private static CurrentAccBalance GetAccBalanceFromDataReader(IDataReader dataReader)
        {
            CurrentAccBalance retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new CurrentAccBalance();

                DateTime day = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["BAL_DATE"].ToString(), out day))
                {
                    retVal.BalDate = day;
                }
                retVal.AccNo = dataReader["ACC_NO"].ToString();
                retVal.DebitCredit = dataReader["ACC_NO"].ToString();
                double Balance = 0;
                if (double.TryParse(dataReader["BALANCE"].ToString(), out Balance))
                    retVal.Balance = Balance;
            }
            return retVal;
        }



        public static void Add(CurrentAccBalance AccBal)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO CURRENT_ACC_BALANCE(BAL_DATE, ACC_NO," +
                    " DEBIT_OR_CREDIT,  BALANCE) VALUES(:v1, :v2, :v3, :v4)";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = AccBal.BalDate;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = AccBal.AccNo;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = AccBal.DebitCredit;
                cmd.Parameters.Add(":v4", OracleType.Double).Value = AccBal.Balance;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(CurrentAccBalance AccBal)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE CURRENT_ACC_BALANCE SET " +
                                  "DEBIT_OR_CREDIT = :v3, BALANCE = :v4 "+
                                  "WHERE BAL_DATE = :v1 and ACC_NO = :v2";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = AccBal.BalDate;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = AccBal.AccNo;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = AccBal.DebitCredit;
                cmd.Parameters.Add(":v4", OracleType.Double).Value = AccBal.Balance;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }


        public static CurrentAccBalance GetAccBalance(DateTime day, String AccNo)
        {
            CurrentAccBalance retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM CURRENT_ACC_BALANCE "+
                    "WHERE TO_CHAR(BAL_DATE, 'dd-mm-yyyy') = '" + day.ToString("dd-MM-yyyy") +
                    "' and ACC_NO = :v1 ";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = AccNo;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetAccBalanceFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }



    }
}
